import { CellObject, readFile, utils, WorkSheet } from "xlsx";
import { toBoolean } from "./dataTypeUtil";

/** 描述一个excel包含的数据 */
export type ExcelDescriptor = {
    sheets: Array<Sheet>
}

export type Sheet = {
    name: string;    // 工作表名称
    fromColumn?: number;     //开始列，从0开始;fromXXX、toXXX均包含；下同；默认0;
    toColumn?: number;   // 结束列, 若超过sheet定义的列数则取最大列数，默认最大列数。
    fromRow?: number;    // 开始行
    toRow?: number;  // 结束行
    columns: Array<Column>  // 从fromColumn处开始
}

export type CellValTypeName = 'string' | 'number' | 'boolean' | 'date' | 'object' | 'enum' | 'array';
export type CellValType = string | number | Date | boolean;

export type Column = {
    index?: number; // 列号，从0开始，未指定时从上一个列号+1顺延; 取值需在fromColumn~toColumn之间（包含首位）
    fieldName: string;  // 该列单元格解析后值对应的字段名
    dataType: CellValTypeName; // 该列单元格值的类型
    enumVals?: Array<CellValType>;  // 枚举值列表，当valueType是enum时需要
    arraySeparator?: string;    // 当valueType是array时需要，用指定的字符将单元格的值分割成数组；默认
    defaultVal?: CellValType;
    useTopValueOnMissing?: boolean; // 当该列的某个单元格未设置时，取上一个单元格的值
    required?: boolean; // 值是否必须，默认false
}


export type RowVal = {
    [k: string]: {
        address: string;    // 单元格地址
        val: any;           // 值
    }
};


export function parse(filePath: string, excelDescriptor: ExcelDescriptor) {
    const wb = readFile(filePath, {
        dense: true // 单元格通过二维数组坐标指定
    });

    let workbookData = new Map<string, Array<RowVal>>();
    for (let sheetDesc of excelDescriptor.sheets) {
        let sheet = wb.Sheets[sheetDesc.name];
        if (!sheet) {
            throw new Error(`sheet '${sheetDesc.name}' not found`);
        }

        let { startCol, endCol, startRow, endRow } = getDataRange(sheet, sheetDesc); // 后续只在指定的范围内读取数据

        let colConfs: Array<Column> = fillColConfs(startCol, endCol, sheetDesc);
        let sheetRows: Array<RowVal> = [];

        let lastRow!: RowVal;
        for (let r = startRow; r <= endRow; r++) {
            let row = sheet[r];
            if (isEmptyRow(row, colConfs, startCol, endCol)) {
                continue;
            }

            let rowVal: RowVal = {}; // 一行对应一个对象
            for (let c = startCol; c <= endCol; c++) {
                let cellConf = colConfs[c];
                if (!cellConf) {
                    continue; // 未配置就不处理
                }
                let cellAddress = utils.encode_cell({ c, r });
                let cell = row[c] as CellObject;
                let cellVal: any = extractCellVal(cell, cellConf, cellAddress, sheetDesc, lastRow);

                rowVal[cellConf.fieldName] = { address: cellAddress, val: cellVal };
            }

            lastRow = rowVal;
            sheetRows.push(rowVal);
        }
        workbookData.set(sheetDesc.name, sheetRows);
    }
    return workbookData;
}

function getDataRange(sheet: WorkSheet, sheetDesc: Sheet) {
    let sheetRangeStr = sheet['!ref'];
    if (!sheetRangeStr) {
        throw new Error(`cannot get sheet range: '${sheetDesc.name}'`);
    }
    let sheetRange = utils.decode_range(sheetRangeStr);

    let maxCol = sheetRange.e.c;
    let maxRow = sheetRange.e.r;

    let startCol = sheetDesc.fromColumn || 0;
    let endCol = sheetDesc.toColumn || maxCol;
    endCol = Math.min(endCol, maxCol);

    let startRow = sheetDesc.fromRow || 0;
    let endRow = sheetDesc.toRow || maxRow;
    endRow = Math.min(endRow, maxRow);

    return { startCol, endCol, startRow, endRow };
}

function convertCellVal(cellVal: any, cellConf: Column, cellAddress: string, sheetDesc: Sheet) {
    let valType = cellConf.dataType;
    if (valType == 'string') {
        cellVal = `${cellVal}`;
    } else if (valType == 'number') {
        let temp = cellVal;
        cellVal = parseFloat(cellVal);
        if (isNaN(cellVal)) {
            throw new Error(`the cell value ('${temp}') cannot be converted to a number ('${cellAddress}' in sheet '${sheetDesc.name}')`);
        }
    } else if (valType == 'boolean') {
        cellVal = toBoolean(cellVal);
    } else if (valType == 'array') {
        let separator = cellConf.arraySeparator || ',';
        cellVal = `${cellVal}`.split(separator);
    } else if (valType == 'enum') {
        let enumVals = cellConf.enumVals || [];
        if (!enumVals.includes(`${cellVal}`)) {
            throw new Error(`the value of field '${cellConf.fieldName}' can only be one of [${enumVals}], actual value is '${cellVal}'`);
        }
        cellVal = `${cellVal}`;
    } else {
        cellVal = `${cellVal}`;
    }
    return cellVal;
}

function extractCellVal(cell: CellObject, cellConf: Column, cellAddress: string, sheetDesc: Sheet, lastRow: RowVal) {
    let cellVal: any;
    if (!cell) {
        if (cellConf.required && !cellConf.useTopValueOnMissing) {
            throw new Error(`the required cell value is missing ('${cellAddress}' in sheet '${sheetDesc.name}')`);
        }
        if (cellConf.useTopValueOnMissing) {
            cellVal = lastRow?.[cellConf.fieldName].val;
        }
        if (cellVal == null && 'defaultVal' in cellConf) {
            cellVal = cellConf.defaultVal;
        }
    } else {
        cellVal = cell.v;
    }

    if (cellVal != null) {
        cellVal = convertCellVal(cellVal, cellConf, cellAddress, sheetDesc);
    }

    return cellVal;
}

function fillColConfs(startCol: number, endCol: number, sheetDesc: Sheet) {
    let colConfs: Array<Column> = []; // 稀疏数组时浪费资源

    let lastIndex = startCol;
    for (let i = 0; i < sheetDesc.columns.length; i++) {
        let col = sheetDesc.columns[i];
        if ('index' in col) {
            let index = col.index as number;
            if (index < startCol || index > endCol) {
                throw new Error(`column (${JSON.stringify(col)}) index should be [${startCol} , ${endCol}]`);
            }
            colConfs[index] = col;
            lastIndex = index + 1;
        } else {
            colConfs[lastIndex++] = col;
        }
    }
    return colConfs;
}


function isEmptyRow(row: Array<any>, colConfs: Array<Column>, startCol: number, endCol: number) {
    if (!row) { // 空行
        return true;
    }
    // 若所在行的目标列都是空，也当做空行处理
    let isBlankRow = true;
    for (let c = startCol; c <= endCol; c++) {
        let cellConf = colConfs[c];
        if (!cellConf) {
            continue;   // 未配置就不处理
        }
        let cell = row[c] as CellObject;
        if (cell) {
            isBlankRow = false;
            break;
        }
    }
    return isBlankRow;
}